package com.pagepub.expo.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;

import com.pagepub.expo.entity.AppDict;
import com.pagepub.expo.entity.IdName;
import com.pagepub.expo.entity.UserRole;
@Repository
public class UserRoleDao {
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate template;
	

	private static String QUERY_ALL = "SELECT userId,roleId FROM App_User_Role";

	
	
	private static String INSERT_SQL = "INSERT  INTO App_User_Role(userId,roleId)"
			+ " VALUES(:userId,:roleId)";

	private static String DELETE_SQL_USER = "DELETE FROM App_User_Role WHERE userId=?";

	private static String DELETE_SQL_ROLE = "DELETE FROM App_User_Role WHERE roleId=?";
	
	private final ParameterizedRowMapper<UserRole> mapper = new ParameterizedRowMapper<UserRole>() {
		public UserRole mapRow(ResultSet rs, int rowNum) throws SQLException {
			UserRole bean = new UserRole();
			bean.setUserId(rs.getLong("userId"));
			bean.setRoleId(rs.getLong("roleId"));
			return bean;
		}
	};

	public List<UserRole> findAllByUser(Long userId) {
		return template.query(QUERY_ALL+" where userId="+userId, mapper);
	}
	
	public List<UserRole> findAllByRole(Long roleId) {
		return template.query(QUERY_ALL+" where roleId="+roleId, mapper);
	}

	
	public void insert(UserRole bean) {

		SqlParameterSource ps = new BeanPropertySqlParameterSource(bean);
		NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
				template.getDataSource());
		npTemplate.update(INSERT_SQL, ps);

	}

	public void deleteByUser(Long id) {
		template.update(DELETE_SQL_USER, id);
	}
	
	public void deleteByRole(Long id) {
		template.update(DELETE_SQL_ROLE, id);
	}


	
}
